package com.sl.au.repository;

import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.ConfigDetailForCRM;
import com.sl.au.entity.Quote;
import com.sl.au.entity.QuoteReplacement;

@Transactional(readOnly = true)
public interface QuoteRepository extends JpaRepository<Quote, String> {
	
	@Query("select c from Quote c where (c.name like '%'+?1+'%' "
			+ " or c.customerName like '%'+?1+'%' "
			+ "or c.memo like '%'+?1+'%') and c.auditState ='通过' ")
	List<Quote> getpagehisquote(String key,Pageable pageable);
	
	
	@Query("select count(*) from Quote c where (c.name like '%'+?1+'%' "
			+ " or c.customerName like '%'+?1+'%' "
			+ "or c.memo like '%'+?1+'%') and c.auditState ='通过' ")
	long getpagehisquoteCount(String key);
	
	@Query("select c from QuoteReplacement c where (c.deviceName like '%'+?1+'%' "
			+ " or c.material like '%'+?1+'%' "
			+ "or c.type like '%'+?1+'%' or c.memo like '%'+?1+'%') and  quote.id=?2 order by c.indexOrder ")
	List<QuoteReplacement> getHisDetails(String key,String opId, Pageable pageable);
	
	
	@Query("select count(*) from QuoteReplacement c where (c.deviceName like '%'+?1+'%' "
			+ " or c.material like '%'+?1+'%' "
			+ "or c.type like '%'+?1+'%' or c.memo like '%'+?1+'%') and  quote.id=?2 ")
	long getHisDetailsCount(String key,String opId);
	
	@Query("select c from ConfigDetailForCRM c where (c.cPartPicNumDes like '%'+?1+'%' "
			+ " or c.cPartName like '%'+?1+'%' "
			+ "or c.cMaterial like '%'+?1+'%' or c.cConfigurationType like '%'+?1+'%') and  opProductPlanId=?2 order by c.cConfigurationType,c.cOrder ")
	List<ConfigDetailForCRM> getDetails(String key,Integer opId, Pageable pageable);
	
	@Query("select c from ConfigDetailForCRM c where opProductPlanId=?1 ")
	List<ConfigDetailForCRM> getDetails(Integer opId, Pageable pageable);
	/**
	 * 根据项目名名称或当前状态查询
	 * 
	 * @param key
	 *            查询条件
	 * @param pageable
	 * @return
	 */
	@Query(" select q from Quote q where q.customerName like '%'+?1+'%' or  q.name like '%'+?1+'%' or q.auditState like '%'+?1+'%' ")
	Page<Quote> findQuote(String key, Pageable pageable);
	
	/**
	 * 查询本部门的报价单
	 * 
	 * @param key
	 *            查询条件
	 * @param pageable
	 * @return
	 */
	@Query(" select q from Quote q where (q.customerName like '%'+?1+'%' or  q.name like '%'+?1+'%' ) and q.user.saleArea.superior =?2 and q.auditState='通过' ")
	Page<Quote> findQuote(String key,String superior, Pageable pageable);
	
	@Query(" select count(*) from Quote q where (q.customerName like '%'+?1+'%' or  q.name like '%'+?1+'%' ) and q.user.saleArea.superior =?2 and q.auditState='通过' ")
	long findQuoteCount(String key,String superiore);

	/**
	 * 得到根据key查询的数据的条数（long）
	 * 
	 * @param key
	 *            查询条件
	 * @return
	 */
	@Query(" select count(*) from Quote q where q.customerName like '%'+?1+'%' or q.name like '%'+?1+'%' or q.auditState like '%'+?1+'%' ")
	long getCountBykey(String key);

	/**
	 * 得到本部门数据(本部门中自己的或审批中，已提交，通过的)
	 * 
	 * @param key
	 *            查询条件
	 * @param superior
	 *            部门
	 * @return page
	 */
	@Query(" select f from Quote f where (f.customerName like '%'+?1+'%' or f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.superior =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))")
	Page<Quote> findSuperiorPage(String key, String superior, String userId, Pageable pageable);

	/**
	 * 得到按本部门过滤的数据条数
	 */
	@Query(" select count(*) from Quote f where (f.customerName like '%'+?1+'%' or f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.superior =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))")
	long findSuperiorCount(String key, String superior, String userId);

	/**
	 * 本片区过滤数据
	 * 
	 * @param key
	 *            查询条件
	 * @param areaId
	 *            saleAreaId
	 * @return
	 */
	@Query(" select f from Quote f where (f.customerName like '%'+?1+'%' or f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.id =?2 and "
			+ "(f.user.id =?3 or(f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))")
	Page<Quote> finAreaPage(String key, String areaId,String userId, Pageable pageable);

	@Query(" select count(*) from Quote f where (f.customerName like '%'+?1+'%' or f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.saleArea.id =?2 and "
			+ "(f.user.id =?3 or (f.auditState='审批中' or f.auditState='已提交' or f.auditState='通过'))")
	long findAreaCount(String key, String areaId,String userId);
	
	/**
	 * 按本人过滤数据
	 * 
	 * @param key
	 *            查询条件
	 * @param userId
	 *            创建者 user id
	 * @return page
	 */
	@Query(" select f from Quote f where (f.customerName like '%'+?1+'%' or f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.id =?2 ")
	Page<Quote> findSelfPage(String key, String userId, Pageable pageable);

	@Query(" select count(*) from Quote f where (f.customerName like '%'+?1+'%' or f.name like '%'+?1+'%' or f.auditState like '%'+?1+'%')"
			+ "and f.user.id =?2 ")
	long findSelfCount(String key, String userId);
	
	
	

	/**
	 * 根据普通价格单Id得到其他细目QuoteReplacement
	 * 
	 * @param quoteId
	 *            普通价格单Id
	 * @return page
	 */
	@Query(" select q from QuoteReplacement q where q.quote.id=?1 ")
	Page<QuoteReplacement> findQuoteReplacement(String quoteId, Pageable pageable);

	/**
	 * 根据普通价格单Id得到其他细目条数
	 */
	@Query(" select count(*) from QuoteReplacement q where q.quote.id=?1 ")
	long getReplacementCount(String quoteId);

	/**
	 * 根据普通价格id得到其他细目List
	 * 
	 * @param quoteId
	 *            普通价格单Id
	 * @return
	 */
	@Query(" select q from QuoteReplacement q where q.quote.id=?1 order by q.indexOrder ")
	List<QuoteReplacement> findReplacementByQuoteId(String quoteId);

	/**
	 * 获得同一个部门 某一种审批状态的报价单
	 */
	@Query(" select q from Quote q where (q.customerName like '%'+?1+'%' or q.name like '%'+?1+'%' )and q.auditState =?2 "
			+ "and q.user.saleArea.superior =?3")
	Page<Quote> findPassQuote(String key, String auditState, String superior, Pageable pageable);

	@Query(" select count(q) from Quote q where (q.customerName like '%'+?1+'%' or q.name like '%'+?1+'%') and q.auditState =?2 "
			+ "and q.user.saleArea.superior =?3")
	long passQuoteCount(String key, String auditState, String superior);

	/**
	 * 根据普通价格单Id得到其他细目QuoteReplacement
	 * 
	 * @param quoteId
	 *            普通价格单Id
	 * @param string 
	 * @return page
	 */
	@Query(" select q from QuoteReplacement q where q.quote.id=?1 and (q.type like '%'+?2+'%' or "
			+ "q.deviceName like '%'+?2+'%' or q.material like '%'+?2+'%') ")
	Page<QuoteReplacement> findQuoteReplacementER(String quoteId, String key, Pageable pageable);

	/**
	 * 根据普通价格单Id得到其他细目条数
	 */
	@Query(" select count(*) from QuoteReplacement q where q.quote.id=?1 and (q.type like '%'+?2+'%' or "
			+ "q.deviceName like '%'+?2+'%' or q.material like '%'+?2+'%') ")
	long getReplacementCountER(String quoteId, String key);

	
	@Query(" select count(*) from QuoteReplacement q where q.type like '%'+?1+'%' and "
			+ "q.deviceName like '%'+?2+'%' and q.material like '%'+?3+'%' and q.quote.customerName like '%'+?4+'%' ")
	long getReplacementCountERprice(String type, String deviceName, String customerName, String material);

	@Query(" select q from QuoteReplacement q where q.type like '%'+?1+'%' and "
			+ "q.deviceName like '%'+?2+'%' and q.material like '%'+?3+'%' and q.quote.customerName like '%'+?4+'%'")
	Page<QuoteReplacement> findQuoteReplacementERprice(String type, String deviceName, String material, String customerName, Pageable pageable);

}
